USE FEDERATION StateFederation(stateid = 0) WITH RESET, FILTERING=OFF
go

--drop table [dbo].[State]
--drop table [dbo].[County]

CREATE TABLE [dbo].[State](
	[StateID] [bigint] NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
(
	[StateID] ASC
)
) federated on (stateid = [StateID])
go


CREATE TABLE [dbo].[County](
	[ID] [bigint] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[StateID] [bigint] NOT NULL,
 CONSTRAINT [PK_County] PRIMARY KEY CLUSTERED 
(
	[ID] ASC, [StateID] ASC
)
) federated on (stateid = [StateID])
GO

ALTER TABLE [dbo].[County]  WITH CHECK ADD  CONSTRAINT [FK_County_State] FOREIGN KEY([StateID])
REFERENCES [dbo].[State] ([StateID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[County] CHECK CONSTRAINT [FK_County_State]
GO


CREATE TABLE [dbo].[City](
	[ID] [bigint] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[StateID] [bigint] NOT NULL,
	[CountyID] [bigint] NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
	[ID] ASC, [StateID] ASC
)
) federated on (stateid = [StateID])
GO

ALTER TABLE [dbo].[City]  WITH CHECK ADD  CONSTRAINT [FK_City_State] FOREIGN KEY([StateID])
REFERENCES [dbo].[State] ([StateID])
ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[City] CHECK CONSTRAINT [FK_City_State]
GO

ALTER TABLE [dbo].[City]  WITH CHECK ADD  CONSTRAINT [FK_City_County] FOREIGN KEY([CountyID], [StateID])
REFERENCES [dbo].[County] ([ID], [StateID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[City] CHECK CONSTRAINT [FK_City_County]
GO
